package com.cruel_wandering.wifi_geo_predictor;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.location.Location;

// imports from the internal classes
import com.cruel_wandering.wifi_geo_predictor.DBEntry;

public class DataBase
{
	
	public static final String TAG = "DB";
	public static final String KEY_ROWID = "_id";
	public static final String KEY_BSSID = "bssid";
	public static final String KEY_RADIUS = "radius";
	
	public static final String KEY_AP_LOCATION_LAT =  "location_lat";
	public static final String KEY_AP_LOCATION_LON =  "location_lon";
	public static final String KEY_AP_LOCATION_ALT =  "location_alt";
	
	public static final String KEY_FIRST_POINT_LAT =  "first_point_lat";
	public static final String KEY_FIRST_POINT_LON =  "first_point_lon";
	public static final String KEY_FIRST_POINT_ALT =  "first_point_alt";
	
	public static final String KEY_SECOND_POINT_LAT = "second_point_lat";
	public static final String KEY_SECOND_POINT_LON = "second_point_lon";
	public static final String KEY_SECOND_POINT_ALT = "second_point_alt";
	
	public static final String KEY_THIRD_POINT_LAT =  "third_point_lat";
	public static final String KEY_THIRD_POINT_LON =  "third_point_lon";
	public static final String KEY_THIRD_POINT_ALT = "second_point_alt";
	
	public static final String KEY_FOURTH_POINT_LAT = "fourth_point_lat";
	public static final String KEY_FOURTH_POINT_LON = "fourth_point_lon";
	public static final String KEY_FOURTH_POINT_ALT = "second_point_alt";

	private static final int DATABASE_VERSION = 2;
	public static final String DATABASE_NAME = "AccessPoints";
	public static final String DATABASE_TABLE = "APs";

	private static final String TABLE_CREATE =
	  "create table IF NOT EXISTS " + DATABASE_TABLE + " (_id integer primary key autoincrement, "
	    + "bssid text, "
	    + "radius integer, " 
	    + "location_lat REAL , "    + "location_lon REAL, "     + "location_alt REAL, "
	    + "first_point_lat REAL, "  + "first_point_lon REAL, "  + "first_point_alt REAL, "
	    + "second_point_lat REAL, " + "second_point_lon REAL, " + "second_point_alt REAL, "
	    + "third_point_lat REAL, "  + "third_point_lon REAL, "  + "third_point_alt REAL, "
	    + "fourth_point_lat REAL, " + "fourth_point_lon REAL, " + "fourth_point_alt REAL );";

	private SQLiteDatabase db;
	private MyDbOpenHelper opener;
	static private DataBase instance = null;

	static public DataBase getInstance(Context ctx)
	{
		if (instance == null)
			instance = new DataBase(ctx);
		return instance;
	}

	private class MyDbOpenHelper extends SQLiteOpenHelper
	{
		public MyDbOpenHelper(Context ctx)
	  {
			super(ctx, DATABASE_NAME, null, DATABASE_VERSION);
	  }

		@Override
		public void onCreate(SQLiteDatabase db)
		{
			db.execSQL(TABLE_CREATE);
		}

		@Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
    }
	}

	private DataBase(Context ctx)
	{
		this.opener = new MyDbOpenHelper(ctx);
	}
	
	public void open()
	{
		db = opener.getWritableDatabase();
		//db.execSQL("drop table if exist APs");
		//db.execSQL(TABLE_CREATE);	
	}
    
    public String getDBName(){
    	return DATABASE_NAME;
    }

    public String getTableName(){
    	return DATABASE_TABLE;
    }
    
    public SQLiteDatabase getDatabase(){
    	return db;
    }

	// ---insert a title into the database---
	public long insertTitle(DBEntry entry)
	{
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_BSSID, entry.getBSSID());
		initialValues.put(KEY_RADIUS, entry.getRadius());
		initialValues.put(KEY_AP_LOCATION_LAT, entry.getAp_location().getLatitude() );
		initialValues.put(KEY_AP_LOCATION_LON, entry.getAp_location().getLongitude() );
		initialValues.put(KEY_AP_LOCATION_ALT, entry.getAp_location().getAltitude() );
		
		initialValues.put(KEY_FIRST_POINT_LAT, entry.getAp_location().getLatitude() );
		initialValues.put(KEY_FIRST_POINT_LON, entry.getAp_location().getLongitude() );
		initialValues.put(KEY_FIRST_POINT_ALT, entry.getAp_location().getAltitude() );
		
		if (entry.pointExist(1)){
			initialValues.put(KEY_SECOND_POINT_LAT, entry.getPoint(1).getLatitude() );
			initialValues.put(KEY_SECOND_POINT_LON, entry.getPoint(1).getLongitude() );
			initialValues.put(KEY_SECOND_POINT_ALT, entry.getPoint(1).getAltitude() );
		}
		if (entry.pointExist(2)){
			initialValues.put(KEY_THIRD_POINT_LAT, entry.getPoint(2).getLatitude() );
			initialValues.put(KEY_THIRD_POINT_LON, entry.getPoint(2).getLongitude() );
			initialValues.put(KEY_THIRD_POINT_ALT, entry.getPoint(2).getAltitude() );
		}
		if (entry.pointExist(3)){
			initialValues.put(KEY_FOURTH_POINT_LAT, entry.getPoint(3).getLatitude() );
			initialValues.put(KEY_FOURTH_POINT_LON, entry.getPoint(3).getLongitude() );
			initialValues.put(KEY_FOURTH_POINT_ALT, entry.getPoint(3).getAltitude() );
		}

		return db.insert(DATABASE_TABLE, null, initialValues);
	}

	// ---deletes a particular title---
	public boolean deleteTitle(String APId)
	{
		return db.delete(DATABASE_TABLE, KEY_BSSID + "=" + "\'" + APId + "\'", null) > 0;
	}

	// ---retrieves all the titles---
	public Cursor getAllTitlesInRange(double fromLat, double tillLat, double fromLon, double tillLon)
	{
		return db
		  .query(DATABASE_TABLE, new String[] { KEY_ROWID, KEY_BSSID, KEY_RADIUS,
				KEY_AP_LOCATION_LAT, KEY_AP_LOCATION_LON, KEY_AP_LOCATION_ALT,
				KEY_FIRST_POINT_LAT, KEY_FIRST_POINT_LON, KEY_FIRST_POINT_ALT,
				KEY_SECOND_POINT_LAT, KEY_SECOND_POINT_LON, KEY_SECOND_POINT_ALT,
				KEY_THIRD_POINT_LAT, KEY_THIRD_POINT_LON, KEY_THIRD_POINT_ALT,
				KEY_FOURTH_POINT_LAT, KEY_FOURTH_POINT_LON, KEY_FOURTH_POINT_ALT
		    },
		    "( " + KEY_AP_LOCATION_LAT + " > " + fromLat
		    + " AND " + KEY_AP_LOCATION_LAT + " < " + tillLat + " )"
		    + " AND " + "( " + KEY_AP_LOCATION_LON + " > " + fromLon
		    + " AND " + KEY_AP_LOCATION_LON + " < " + tillLon + " )",
		    null,
		    null,
		    null,
		    null,
		    null);
	}

	// ---retrieves a particular title---
	public Cursor getTitle(String APId) throws SQLException
	{
		Cursor mCursor =
		  db.query(true, DATABASE_TABLE, new String[] { KEY_ROWID, KEY_BSSID, KEY_RADIUS,
			    KEY_AP_LOCATION_LAT, KEY_AP_LOCATION_LON, KEY_AP_LOCATION_ALT,
				KEY_FIRST_POINT_LAT, KEY_FIRST_POINT_LON, KEY_FIRST_POINT_ALT,
				KEY_SECOND_POINT_LAT, KEY_SECOND_POINT_LON, KEY_SECOND_POINT_ALT,
				KEY_THIRD_POINT_LAT, KEY_THIRD_POINT_LON, KEY_THIRD_POINT_ALT,
				KEY_FOURTH_POINT_LAT, KEY_FOURTH_POINT_LON, KEY_FOURTH_POINT_ALT
		  	},
		  	KEY_BSSID + " = " + "\'" + APId + "\'",
		    null, 
		    null, 
		    null, 
		    null,
		    null);
		
		if (mCursor != null){
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	// ---updates a title---
	public boolean updateTitleAddPointLocation(String APId, Location point, int index)
	{
		ContentValues args = new ContentValues();
		switch (index)
		{
		case 0:
			args.put(KEY_AP_LOCATION_LAT, point.getLatitude());
			args.put(KEY_AP_LOCATION_LON, point.getLongitude());
			args.put(KEY_AP_LOCATION_ALT, point.getAltitude());
			break;
		case 1:
			args.put(KEY_FIRST_POINT_LAT, point.getLatitude());
			args.put(KEY_FIRST_POINT_LON, point.getLongitude());
			args.put(KEY_FIRST_POINT_ALT, point.getAltitude());
			break;
		case 2:
			args.put(KEY_SECOND_POINT_LAT, point.getLatitude());
			args.put(KEY_SECOND_POINT_LON, point.getLongitude());
			args.put(KEY_SECOND_POINT_ALT, point.getAltitude());
			break;
		case 3:
			args.put(KEY_THIRD_POINT_LAT, point.getLatitude());
			args.put(KEY_THIRD_POINT_LON, point.getLongitude());
			args.put(KEY_THIRD_POINT_ALT, point.getAltitude());
			break;
		case 4:
			args.put(KEY_FOURTH_POINT_LAT, point.getLatitude());
			args.put(KEY_FOURTH_POINT_LON, point.getLongitude());
			args.put(KEY_FOURTH_POINT_ALT, point.getAltitude());
			break;
		default:
			break; // TODO !!!
		}
		return db.update(DATABASE_TABLE, args, KEY_BSSID + " = " + "\'" + APId + "\'", null) > 0;
	}
	
	
	// ---updates a title---
	public boolean updateTitleAddPointValue(String APId, double lat, double lng, int index)
	{
		ContentValues args = new ContentValues();
		switch (index)
		{
		case 0:
			args.put(KEY_AP_LOCATION_LAT, lat);
			args.put(KEY_AP_LOCATION_LON, lng);
			break;
		case 1:
			args.put(KEY_FIRST_POINT_LAT, lat);
			args.put(KEY_FIRST_POINT_LON, lng);
			break;
		case 2:
			args.put(KEY_SECOND_POINT_LAT, lat);
			args.put(KEY_SECOND_POINT_LON, lng);
			break;
		case 3:
			args.put(KEY_THIRD_POINT_LAT, lat);
			args.put(KEY_THIRD_POINT_LON, lng);
			break;
		case 4:
			args.put(KEY_FOURTH_POINT_LAT, lat);
			args.put(KEY_FOURTH_POINT_LON, lng);
			break;
		default:
			break; // TODO !!!
		}
		return db.update(DATABASE_TABLE, args, KEY_BSSID + " = " + "\'" + APId + "\'", null) > 0;
	}

	// ---updates a title---
	public boolean updateTitleRadius(String APId, int radius)
	{
		ContentValues args = new ContentValues();
		args.put(KEY_RADIUS, radius);
		return db.update(DATABASE_TABLE, args, KEY_BSSID + " = " + "\'" + APId + "\'", null) > 0;
	}
}
